
/****** Object:  Stored Procedure [dbo].FriendsDelete    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spFriendsDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spFriendsDelete]
GO

/****** Object:  Stored Procedure [dbo].FriendsGet    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spFriendsGet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spFriendsGet]
GO

/****** Object:  Stored Procedure [dbo].FriendsGetList    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spFriendsGetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spFriendsGetList]
GO

	
/****** Object:  Stored Procedure [dbo].FriendsInsert    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spFriendsInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spFriendsInsert]
GO

/****** Object:  Stored Procedure [dbo].FriendsUpdate    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spFriendsUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spFriendsUpdate]
GO


/******************************************************************************
**		File: 
**		Name: [dbo].spFriendsDelete
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spFriendsDelete
	@UserId int,
	@FriendId int
AS

DELETE FROM [dbo].[Friends]
WHERE
	[UserId] = @UserId
	AND [FriendId] = @FriendId
GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].spFriendsGet
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spFriendsGet
	@UserId int,
	@FriendId int
AS

SELECT
	[UserId],
	[FriendId],
	[Allow]
FROM
	[dbo].[Friends]
WHERE
	[UserId] = @UserId
	AND [FriendId] = @FriendId

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].spFriendsGetList
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spFriendsGetList
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TempFriends (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	UserId int,	
	FriendId int	
)

DECLARE @sql nvarchar(2000)
DECLARE @Top int

SET @Top = @Page*@PageSize
IF @PageSize > 0
   SET ROWCOUNT @Top
-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TempFriends ([UserId],[FriendId]) SELECT '
SET @sql = @sql + ' [UserId],[FriendId] FROM [dbo].[Friends]  ORDER BY [' + @OrderBy + N'] ' + @OrderDirection
EXEC (@sql)
SET ROWCOUNT 0

SELECT @TotalRecords = COUNT(*) FROM [Friends]

SELECT
	[dbo].[Friends].[UserId],
	[dbo].[Friends].[FriendId],
	[Allow]
FROM
	#TempFriends AS tblTemp JOIN [dbo].[Friends] ON
	tblTemp.UserId = [dbo].[Friends].UserId  AND 	
	tblTemp.FriendId = [dbo].[Friends].FriendId 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TempFriends

GO

/******************************************************************************
**		File: 
**		Name: [dbo].spFriendsInsert
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spFriendsInsert
	@UserId int,
	@FriendId int,
	@Allow bit
AS

-- THIS STORED PROCEDURE NEEDS TO BE MANUALLY COMPLETED
-- MULITPLE PRIMARY KEY MEMBERS OR NON-GUID/INT PRIMARY KEY

INSERT INTO [dbo].[Friends] (
	[UserId],
	[FriendId],
	[Allow]
) VALUES (
	@UserId,
	@FriendId,
	@Allow
)

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].spFriendsUpdate
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spFriendsUpdate
	@UserId int, 
	@FriendId int, 
	@Allow bit 
AS

UPDATE [dbo].[Friends] SET
	[Allow] = @Allow
WHERE
	[UserId] = @UserId
	AND [FriendId] = @FriendId

GO


/****** Object:  Stored Procedure [dbo].GiftsDelete    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spGiftsDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spGiftsDelete]
GO

/****** Object:  Stored Procedure [dbo].GiftsGet    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spGiftsGet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spGiftsGet]
GO

/****** Object:  Stored Procedure [dbo].GiftsGetList    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spGiftsGetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spGiftsGetList]
GO

	
/****** Object:  Stored Procedure [dbo].GiftsInsert    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spGiftsInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spGiftsInsert]
GO

/****** Object:  Stored Procedure [dbo].GiftsUpdate    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spGiftsUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spGiftsUpdate]
GO


/******************************************************************************
**		File: 
**		Name: [dbo].spGiftsDelete
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spGiftsDelete
	@GiftId int
AS

DELETE FROM [dbo].[Gifts]
WHERE
	[GiftId] = @GiftId
GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].spGiftsGet
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spGiftsGet
	@GiftId int
AS

SELECT
	[GiftId],
	[Name],
	[Price],
	[Description],
	[CreationDate]
FROM
	[dbo].[Gifts]
WHERE
	[GiftId] = @GiftId

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].spGiftsGetList
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spGiftsGetList
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TempGifts (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	GiftId int	
)

DECLARE @sql nvarchar(2000)
DECLARE @Top int

SET @Top = @Page*@PageSize
IF @PageSize > 0
   SET ROWCOUNT @Top
-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TempGifts ([GiftId]) SELECT '
SET @sql = @sql + ' [GiftId] FROM [dbo].[Gifts]  ORDER BY [' + @OrderBy + N'] ' + @OrderDirection
EXEC (@sql)
SET ROWCOUNT 0

SELECT @TotalRecords = COUNT(*) FROM [Gifts]

SELECT
	[dbo].[Gifts].[GiftId],
	[Name],
	[Price],
	[Description],
	[CreationDate]
FROM
	#TempGifts AS tblTemp JOIN [dbo].[Gifts] ON
	tblTemp.GiftId = [dbo].[Gifts].GiftId 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TempGifts

GO

/******************************************************************************
**		File: 
**		Name: [dbo].spGiftsInsert
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spGiftsInsert
	@Name nvarchar(50),
	@Price int,
	@Description ntext,
	@CreationDate datetime,
	@GiftId int output
AS

INSERT INTO [dbo].[Gifts] 
(
	[Name],
	[Price],
	[Description],
	[CreationDate]
) 
VALUES 
(
	@Name,
	@Price,
	@Description,
	@CreationDate
)

SELECT @GiftId = SCOPE_IDENTITY()

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].spGiftsUpdate
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spGiftsUpdate
	@GiftId int, 
	@Name nvarchar(50), 
	@Price int, 
	@Description ntext, 
	@CreationDate datetime 
AS

UPDATE [dbo].[Gifts] SET
	[Name] = @Name,
	[Price] = @Price,
	[Description] = @Description,
	[CreationDate] = @CreationDate
WHERE
	[GiftId] = @GiftId

GO


/****** Object:  Stored Procedure [dbo].HistoriesDelete    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spHistoriesDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spHistoriesDelete]
GO

/****** Object:  Stored Procedure [dbo].HistoriesGet    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spHistoriesGet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spHistoriesGet]
GO

/****** Object:  Stored Procedure [dbo].HistoriesGetList    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spHistoriesGetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spHistoriesGetList]
GO

	
/****** Object:  Stored Procedure [dbo].HistoriesInsert    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spHistoriesInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spHistoriesInsert]
GO

/****** Object:  Stored Procedure [dbo].HistoriesUpdate    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spHistoriesUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spHistoriesUpdate]
GO


/******************************************************************************
**		File: 
**		Name: [dbo].spHistoriesDelete
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spHistoriesDelete
	@HistoryId int
AS

DELETE FROM [dbo].[Histories]
WHERE
	[HistoryId] = @HistoryId
GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].spHistoriesGet
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spHistoriesGet
	@HistoryId int
AS

SELECT
	[HistoryId],
	[ReceiverId],
	[SenderId],
	[Message],
	[CreationDate]
FROM
	[dbo].[Histories]
WHERE
	[HistoryId] = @HistoryId

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].spHistoriesGetList
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spHistoriesGetList
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TempHistories (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	HistoryId int	
)

DECLARE @sql nvarchar(2000)
DECLARE @Top int

SET @Top = @Page*@PageSize
IF @PageSize > 0
   SET ROWCOUNT @Top
-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TempHistories ([HistoryId]) SELECT '
SET @sql = @sql + ' [HistoryId] FROM [dbo].[Histories]  ORDER BY [' + @OrderBy + N'] ' + @OrderDirection
EXEC (@sql)
SET ROWCOUNT 0

SELECT @TotalRecords = COUNT(*) FROM [Histories]

SELECT
	[dbo].[Histories].[HistoryId],
	[ReceiverId],
	[SenderId],
	[Message],
	[CreationDate]
FROM
	#TempHistories AS tblTemp JOIN [dbo].[Histories] ON
	tblTemp.HistoryId = [dbo].[Histories].HistoryId 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TempHistories

GO

/******************************************************************************
**		File: 
**		Name: [dbo].spHistoriesInsert
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spHistoriesInsert
	@ReceiverId int,
	@SenderId int,
	@Message nvarchar(200),
	@CreationDate datetime,
	@HistoryId int output
AS

INSERT INTO [dbo].[Histories] 
(
	[ReceiverId],
	[SenderId],
	[Message],
	[CreationDate]
) 
VALUES 
(
	@ReceiverId,
	@SenderId,
	@Message,
	@CreationDate
)

SELECT @HistoryId = SCOPE_IDENTITY()

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].spHistoriesUpdate
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spHistoriesUpdate
	@HistoryId int, 
	@ReceiverId int, 
	@SenderId int, 
	@Message nvarchar(200), 
	@CreationDate datetime 
AS

UPDATE [dbo].[Histories] SET
	[ReceiverId] = @ReceiverId,
	[SenderId] = @SenderId,
	[Message] = @Message,
	[CreationDate] = @CreationDate
WHERE
	[HistoryId] = @HistoryId

GO


/****** Object:  Stored Procedure [dbo].PresentsDelete    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spPresentsDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spPresentsDelete]
GO

/****** Object:  Stored Procedure [dbo].PresentsGet    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spPresentsGet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spPresentsGet]
GO

/****** Object:  Stored Procedure [dbo].PresentsGetList    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spPresentsGetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spPresentsGetList]
GO

	
/****** Object:  Stored Procedure [dbo].PresentsInsert    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spPresentsInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spPresentsInsert]
GO

/****** Object:  Stored Procedure [dbo].PresentsUpdate    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spPresentsUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spPresentsUpdate]
GO

/****** Object:  Stored Procedure [dbo].spPresentsGetListByReceiverId    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spPresentsGetListByReceiverId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spPresentsGetListByReceiverId]
GO		


/******************************************************************************
**		File: 
**		Name: [dbo].spPresentsDelete
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spPresentsDelete
	@PresentId int
AS

DELETE FROM [dbo].[Presents]
WHERE
	[PresentId] = @PresentId
GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].spPresentsGet
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spPresentsGet
	@PresentId int
AS

SELECT
	[PresentId],
	[Amount],
	[Type],
	[Message],
	[SenderId],
	[ReceiverId],
	[CreationDate],
	[GiftId]
FROM
	[dbo].[Presents]
WHERE
	[PresentId] = @PresentId

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].spPresentsGetList
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spPresentsGetList
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TempPresents (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	PresentId int	
)

DECLARE @sql nvarchar(2000)
DECLARE @Top int

SET @Top = @Page*@PageSize
IF @PageSize > 0
   SET ROWCOUNT @Top
-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TempPresents ([PresentId]) SELECT '
SET @sql = @sql + ' [PresentId] FROM [dbo].[Presents]  ORDER BY [' + @OrderBy + N'] ' + @OrderDirection
EXEC (@sql)
SET ROWCOUNT 0

SELECT @TotalRecords = COUNT(*) FROM [Presents]

SELECT
	[dbo].[Presents].[PresentId],
	[Amount],
	[Type],
	[Message],
	[SenderId],
	[ReceiverId],
	[CreationDate],
	[GiftId]
FROM
	#TempPresents AS tblTemp JOIN [dbo].[Presents] ON
	tblTemp.PresentId = [dbo].[Presents].PresentId 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TempPresents

GO

/******************************************************************************
**		File: 
**		Name: [dbo].spPresentsInsert
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spPresentsInsert
	@Amount int,
	@Type int,
	@Message ntext,
	@SenderId int,
	@ReceiverId int,
	@CreationDate datetime,
	@GiftId int,
	@PresentId int output
AS

INSERT INTO [dbo].[Presents] 
(
	[Amount],
	[Type],
	[Message],
	[SenderId],
	[ReceiverId],
	[CreationDate],
	[GiftId]
) 
VALUES 
(
	@Amount,
	@Type,
	@Message,
	@SenderId,
	@ReceiverId,
	@CreationDate,
	@GiftId
)

SELECT @PresentId = SCOPE_IDENTITY()

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].spPresentsUpdate
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spPresentsUpdate
	@PresentId int, 
	@Amount int, 
	@Type int, 
	@Message ntext, 
	@SenderId int, 
	@ReceiverId int, 
	@CreationDate datetime, 
	@GiftId int 
AS

UPDATE [dbo].[Presents] SET
	[Amount] = @Amount,
	[Type] = @Type,
	[Message] = @Message,
	[SenderId] = @SenderId,
	[ReceiverId] = @ReceiverId,
	[CreationDate] = @CreationDate,
	[GiftId] = @GiftId
WHERE
	[PresentId] = @PresentId

GO


/******************************************************************************
**		File: 
**		Name: [dbo].spPresentsGetListByReceiverId
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spPresentsGetListByReceiverId
	@ReceiverId int,
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TempPresents (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	PresentId int	
)

DECLARE @sql nvarchar(2000)
DECLARE @sqlParameters nvarchar(2000)
DECLARE @Top int

SET @Top = @Page*@PageSize
IF @PageSize > 0
   SET ROWCOUNT @Top
-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TempPresents ([PresentId]) SELECT '
SET @sql = @sql + ' [PresentId] FROM [dbo].[Presents] '
SET @sql = @sql + 'WHERE [ReceiverId] = @ReceiverId '
SET @sql = @sql + 'ORDER BY [' + @OrderBy + N'] ' + @OrderDirection

SET @sqlParameters = N'@ReceiverId int'
EXEC sp_executesql @sql, @sqlParameters, @ReceiverId = @ReceiverId

SET ROWCOUNT 0

SELECT @TotalRecords = COUNT(*) FROM [Presents] WHERE [ReceiverId] = @ReceiverId

SELECT
	[dbo].[Presents].[PresentId],
	[Amount],
	[Type],
	[Message],
	[SenderId],
	[ReceiverId],
	[CreationDate],
	[GiftId]
FROM
	#TempPresents AS tblTemp JOIN [dbo].[Presents] ON
	tblTemp.PresentId = [dbo].[Presents].PresentId 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TempPresents

GO			
/****** Object:  Stored Procedure [dbo].UsersDelete    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spUsersDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spUsersDelete]
GO

/****** Object:  Stored Procedure [dbo].UsersGet    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spUsersGet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spUsersGet]
GO

/****** Object:  Stored Procedure [dbo].UsersGetList    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spUsersGetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spUsersGetList]
GO

	
/****** Object:  Stored Procedure [dbo].UsersInsert    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spUsersInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spUsersInsert]
GO

/****** Object:  Stored Procedure [dbo].UsersUpdate    Script Date: Monday, July 27, 2009 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spUsersUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spUsersUpdate]
GO


/******************************************************************************
**		File: 
**		Name: [dbo].spUsersDelete
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spUsersDelete
	@UserId int
AS

DELETE FROM [dbo].[Users]
WHERE
	[UserId] = @UserId
GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].spUsersGet
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spUsersGet
	@UserId int
AS

SELECT
	[UserId],
	[Email],
	[Cash],
	[CreationDate],
	[LastLogin]
FROM
	[dbo].[Users]
WHERE
	[UserId] = @UserId

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].spUsersGetList
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spUsersGetList
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TempUsers (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	UserId int	
)

DECLARE @sql nvarchar(2000)
DECLARE @Top int

SET @Top = @Page*@PageSize
IF @PageSize > 0
   SET ROWCOUNT @Top
-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TempUsers ([UserId]) SELECT '
SET @sql = @sql + ' [UserId] FROM [dbo].[Users]  ORDER BY [' + @OrderBy + N'] ' + @OrderDirection
EXEC (@sql)
SET ROWCOUNT 0

SELECT @TotalRecords = COUNT(*) FROM [Users]

SELECT
	[dbo].[Users].[UserId],
	[Email],
	[Cash],
	[CreationDate],
	[LastLogin]
FROM
	#TempUsers AS tblTemp JOIN [dbo].[Users] ON
	tblTemp.UserId = [dbo].[Users].UserId 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TempUsers

GO

/******************************************************************************
**		File: 
**		Name: [dbo].spUsersInsert
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spUsersInsert
	@Email nvarchar(50),
	@Cash int,
	@CreationDate datetime,
	@LastLogin datetime,
	@UserId int output
AS

INSERT INTO [dbo].[Users] 
(
	[Email],
	[Cash],
	[CreationDate],
	[LastLogin]
) 
VALUES 
(
	@Email,
	@Cash,
	@CreationDate,
	@LastLogin
)

SELECT @UserId = SCOPE_IDENTITY()

GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].spUsersUpdate
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 7/27/2009 4:18:22 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE [dbo].spUsersUpdate
	@UserId int, 
	@Email nvarchar(50), 
	@Cash int, 
	@CreationDate datetime, 
	@LastLogin datetime 
AS

UPDATE [dbo].[Users] SET
	[Email] = @Email,
	[Cash] = @Cash,
	[CreationDate] = @CreationDate,
	[LastLogin] = @LastLogin
WHERE
	[UserId] = @UserId

GO


	